package com.stylefeng.guns.modular.system.jqgrid;

import java.text.SimpleDateFormat;

import java.util.Date;
import java.util.List;
import java.util.Map;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.Row;
import org.springframework.web.multipart.MultipartFile;

import com.stylefeng.guns.core.page.JqGridColModel;
import com.stylefeng.guns.core.util.ToolUtil;


public class ExcelUtil<T>{

	public int insertDBByExcel(MultipartFile excelFile) {
		int count = 0;
		try {
			//使用POI解析Excel文件  
			HSSFWorkbook book = new HSSFWorkbook(excelFile.getInputStream());
			//根据名称获得指定Sheet对象  
			HSSFSheet  hssfSheet = book.getSheet("数据");
			if (hssfSheet != null) {
					System.out.println(hssfSheet.getLastRowNum());
				for (Row row : hssfSheet) {  
		            //获取每一行的每个单元格  
		            String id = row.getCell(0).getStringCellValue();  
		            String province = row.getCell(1).getStringCellValue();  
		              
		            System.out.println(id + province  +"--");
		            
		            count++;
		        }  
				}
				// TODO 正式导入数据库
				
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		return count;	
	}

	/**
	 *   根据jqgrid的格式、标题设置，输出EXCEL
	 * @param response 输出
	 * @param data 输出到EXCEL的LIST
	 * @param exportPage 标题模型（比如列转中文，宽度定义等。）
	 */	

	public void exportToExcel(HttpServletResponse response, List<Map<String, Object>> data,
			ExportPage<T> exportPage) {
		ServletOutputStream out = null;
		try {
			//第一步：查询所有的数据  
			  
			//第二步：使用POI将数据写到Excel文件中  
			//在内存中创建一个Excel文件  
			@SuppressWarnings("resource")
			HSSFWorkbook workBook = new HSSFWorkbook();  
			
			//HSSFCellStyle style = style.createCellStyle();
			HSSFCellStyle style = workBook.createCellStyle();   
			//创建一个标签页  
			HSSFSheet sheet = workBook.createSheet(exportPage.getTitle());
			
			//设置其它数据 设置风格
			style.setAlignment(HorizontalAlignment.LEFT);		
		    style.setBorderBottom(BorderStyle.THIN); // 设置单无格的边框为粗体  下边框
		    style.setBottomBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex()); // 设置单元格的边框颜色． 
		    style.setBorderLeft(BorderStyle.THIN); //左边框
		    style.setLeftBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex()); 
		    style.setBorderRight(BorderStyle.THIN); //右边框
		    style.setRightBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex()); 
		    style.setBorderTop(BorderStyle.THIN); //上边框
		    style.setTopBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex()); 
		   // style.setWrapText(true);//文本区域随内容多少自动调整 
		    
			//创建标题行  
		    List<JqGridColModel> colModels = exportPage.getColModel();
			HSSFRow headRow = sheet.createRow(0);
			for (int i = 0; i < colModels.size(); i++) {
				HSSFCell cell = headRow.createCell(i);
				cell.setCellValue(colModels.get(i).getLabel());  
				sheet.setColumnWidth(i, colModels.get(i).getWidth()/20 * 256);	//列宽，1个字符占256单位
				cell.setCellStyle(style);
			}
			
			for (Map<String, Object> map : data) {
			    HSSFRow dataRow = sheet.createRow(sheet.getLastRowNum() + 1);	//行  
				for (String key : map.keySet()) {
					int j = 0;	//列
					for (JqGridColModel colModel : colModels) {
						if (key.equalsIgnoreCase(colModel.getName())) {	//在对应的列填充数据
							HSSFCell cell = dataRow.createCell(j);
							Object val = map.get(key);
							//getClass().getSimpleName().toString()
							System.out.println(val.getClass().getSimpleName());
							cell.setCellValue(val.toString());  
							cell.setCellStyle(style);
							if (ToolUtil.getType(val).equals("String") )
							{
								cell.setCellType(CellType.STRING);
							}
							else if (ToolUtil.isNum(val)) {
								//cell.setCellType(CellType.NUMERIC);
							}
							else if (ToolUtil.getType(val).equals("Date") || ToolUtil.getType(val).equals("Timestamp")) {
								//TODO 日期格式精细化
								cell.setCellType(CellType.STRING);
							}
							else {
								cell.setCellType(CellType.STRING);
							}
						}
						j++;
					}

				}
			}  
			  
			// 文件名
			String strFileSubfix = new SimpleDateFormat("yyMMddHHmmssSSS").format(new Date()).toString();
			StringBuffer fileName = new StringBuffer(exportPage.getTitle() + "_");
			fileName.append(strFileSubfix);
			fileName.append(".xls");
			response.reset();
			out = response.getOutputStream();
			//获取客户端浏览器类型
			//response.setContentType("application/vnd.ms-excel;charset=UTF-8");
            //response.setHeader("Content-disposition", "attachment; filename=" + fileName.toString());
			response.setHeader("content-disposition", "attachment; filename=\""
					+ new String(fileName.toString().getBytes("utf-8"),
							"ISO8859_1") + "\"");
			
			workBook.write(out);
			out.flush();
			out.close();
			
			
		} catch (Exception e) {
			e.printStackTrace();
		}
		
	}
}
